﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMonitor_Who]
#-- Purpose:		sp_who - with additional info; using DMOs
#--	Last Update:	05/03/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMonitor_Who]
AS

SET NOCOUNT ON;

DECLARE	@major int, @sSQL varchar(MAX), @sTemplate varchar(MAX)

SELECT	@major = CAST(PARSENAME(CONVERT(varchar,SERVERPROPERTY('ProductVersion')),4) AS int),
		@sTemplate = 'WITH sess AS (
	SELECT		sdes.*,
				sdec.most_recent_sql_handle,
				sdec.auth_scheme,
				sdec.net_transport,
				sdec.client_net_address,
				sdec.net_packet_size,
				sdec.last_read,
				sdec.last_write,
				h1.text
	FROM		sys.dm_exec_sessions sdes
	JOIN		sys.dm_exec_connections sdec ON sdes.session_id = sdec.session_id
	CROSS APPLY sys.dm_exec_sql_text(sdec.most_recent_sql_handle) AS h1
)
SELECT		sess.session_id,
			sess.status,
			sess.host_name,
			sess.program_name,
			sess.login_name,
			sess.client_interface_name,
			(CASE WHEN ISNULL(sess.last_read, sess.last_write) > sess.last_write THEN sess.last_read ELSE sess.last_write END) last_query_time,
			[VAR_OPENTRAN] open_transaction_count,
			sess.cpu_time,
			(sess.reads + sess.writes) disk_io,
			sess.text,

			sdowt.blocking_session_id,
			sessB.status blocking_status,
			sessB.host_name blocking_host_name,
			sessB.program_name blocking_program_name,
			sessB.login_name blocking_login_name,
			sessB.client_interface_name blocking_client_interface_name,
			(CASE WHEN ISNULL(sessB.last_read, sessB.last_write) > sessB.last_write THEN sessB.last_read ELSE sessB.last_write END) blocking_last_query_time,
			[VAR_OPENTRANBLOCK] blocking_open_transaction_count,
			sessB.cpu_time blocking_cpu_time,
			(sessB.reads + sessB.writes) blocking_disk_io,
			sessB.text blocking_text
FROM		sess
LEFT JOIN	sys.dm_os_waiting_tasks sdowt ON sess.session_id = sdowt.session_id
LEFT JOIN	sess sessB ON sdowt.blocking_session_id = sessB.session_id
WHERE		sess.is_user_process = 1
			OR sdowt.blocking_session_id IS NOT NULL
ORDER BY	sess.client_net_address,
			sess.session_id'

IF @major > 10
	SELECT	@sSQL = REPLACE(REPLACE(@sTemplate, '[VAR_OPENTRAN]', 'sess.open_transaction_count'), '[VAR_OPENTRANBLOCK]', 'sessB.open_transaction_count')
ELSE
	SELECT	@sSQL = REPLACE(REPLACE(@sTemplate, '[VAR_OPENTRAN]', 'CAST(NULL as int)'), '[VAR_OPENTRANBLOCK]', 'CAST(NULL as int)')

EXEC	(@sSQL)

SET NOCOUNT OFF;
